﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Module;
using System.Data.SqlClient;
using Qmaker.DAL;
using System.Data;

namespace DLL
{
    public class DUserPassport
    {
        #region SQL语句

        #region 插入
        private const string SQL_INSERT = "INSERT INTO UserPassport(UserPassportId,UserName,Password,Status,LoginCount,LastLogin,DateCreated)VALUES(@UserPassportId,@UserName,@Password,@Status,@LoginCount,@LastLogin,@DateCreated)";
        #endregion

        #region 修改
        private const string SQL_UPDATE = "UPDATE UserPassport   SET UserName = @UserName,Password = @Password,Status=@Status,LoginCount = @LoginCount,LastLogin = @LastLogin,DateCreated = @DateCreated WHERE  UserPassportId = @UserPassportId";
        #endregion

        #region 删除
        private const string SQL_DELETE = "delete from UserPassport where  UserPassportId=@UserPassportId";
        #endregion

        #region 查询

        private const string SQL_SELECT_USERNAME_PASSWORD = "SELECT UserPassportId,UserName,Password,Status,LoginCount,LastLogin,DateCreated FROM UserPassport WHERE UserName=@UserName AND Password=@Password";

        private const string SQL_SELECT_USERNAME = "SELECT UserPassportId,UserName,Password,Status,LoginCount,LastLogin,DateCreated FROM UserPassport WHERE UserName=@UserName";

        private const string SQL_SELECT_USERID = "SELECT UserPassportId,UserName,Password,Status,LoginCount,LastLogin,DateCreated FROM UserPassport WHERE UserPassportId=@UserPassportId";

        private const string SQL_SELECT_ALL = "SELECT UserPassportId,UserName,Password,Status,LoginCount,LastLogin,DateCreated FROM UserPassport";
        
        private const string SQL_SELECT_STATUS = "SELECT UserPassportId,UserName,Password,Status,LoginCount,LastLogin,DateCreated FROM UserPassport WHERE Status=@Status";

        private const string SQL_SELECT_PASSPORT_BY_USERTYPE = "SELECT u1.UserPassportId,u1.UserName,u1.Password,u1.Status,u1.LoginCount,u1.LastLogin,u1.DateCreated FROM UserPassport u1 inner join UserProfile u2 On u1.UserPassportId = u2.UserPassportId where u1.Status=1 and u2.UserType = @UserType order by u1.UserName";
       
        #endregion

        #endregion

        #region 公共接口

        ///<summary>
        /// 添加
        ///</summary>
        ///<param name="blogInfo">UserPassportInfo对象</param>
        public bool Insert(UserPassportInfo userInfo)
        {
            if (userInfo == null)
                throw new ArgumentNullException("userInfo");

            SqlParameter[] parms = GetInsertParms(userInfo);
            //parms = SetInsertParmsCheck(parms);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        int num = Convert.ToInt32(SqlHelper.ExecuteScalar(trans, CommandType.Text, SQL_INSERT, parms));
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="userInfo"></param>
        public bool Update(UserPassportInfo userInfo)
        {
            if (userInfo == null || userInfo.UserPassportId.ToString().Length != 18)
                throw new ArgumentNullException("userInfo");

            SqlParameter[] parms = GetInsertParms(userInfo);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE, parms);
                        trans.Commit();
                    }
                    catch(Exception ex)
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <param name="userPassportId"></param>
        /// <returns></returns>
        public UserPassportInfo GetUserPassportInfo(long userPassportId)
        {
            UserPassportInfo userPassportInfo = new UserPassportInfo();
            SqlParameter[] parms = {
                    new SqlParameter("@UserPassportId",SqlDbType.BigInt)
                };
            parms[0].Value = userPassportId;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_USERID, parms))
            {
                while (reader.Read())
                {
                    userPassportInfo = GetInstanceByDataReader(reader);

                }
            }
            return userPassportInfo;
        }

        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public UserPassportInfo GetUserPassportInfo(string userName, string password)
        {
            UserPassportInfo userPassportInfo = new UserPassportInfo();
            SqlParameter[] parms = {
                    new SqlParameter("@UserName",SqlDbType.VarChar,40),
                    new SqlParameter("@Password",SqlDbType.VarChar,20)
                };
            parms[0].Value = userName;
            parms[1].Value = password;


            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_USERNAME_PASSWORD, parms))
            {
                while (reader.Read())
                {
                    userPassportInfo = GetInstanceByDataReader(reader);

                }
            }
            return userPassportInfo;
        }

        /// <summary>
        /// 根据用户名查找用户
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public UserPassportInfo GetUserPassportInfo(string userName)
        {
            UserPassportInfo userPassportInfo = new UserPassportInfo();
            SqlParameter[] parms = {
                    new SqlParameter("@UserName",SqlDbType.VarChar,40)
                };
            parms[0].Value = userName;


            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_USERNAME, parms))
            {
                while (reader.Read())
                {
                    userPassportInfo = GetInstanceByDataReader(reader);

                }
            }
            return userPassportInfo;
        }

        /// <summary>
        /// 获取所有用户
        /// </summary>
        /// <returns></returns>
        public List<UserPassportInfo> GetAllUserPassportInfo()
        {
            List<UserPassportInfo> result = new List<UserPassportInfo>();

            UserPassportInfo userPassportInfo = null;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_ALL, null))
            {
                while (reader.Read())
                {
                    userPassportInfo = GetInstanceByDataReader(reader);
                    if (userPassportInfo != null && userPassportInfo.UserPassportId.ToString().Length == 18)
                        result.Add(userPassportInfo);
                }
            }
            return result;
        }

        /// <summary>
        /// 根据用户状态获取用户列表
        /// </summary>
        /// <param name="status"></param>
        /// <returns></returns>
        public List<UserPassportInfo> GetUserPassportInfoByStatus(int status)
        {
            List<UserPassportInfo> result = new List<UserPassportInfo>();
            SqlParameter[] parms = {
                    new SqlParameter("@Status",SqlDbType.VarChar,40)
                };
            parms[0].Value = status;

            UserPassportInfo userPassportInfo = null;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_STATUS, parms))
            {
                while (reader.Read())
                {
                    userPassportInfo = GetInstanceByDataReader(reader);
                    if (userPassportInfo != null && userPassportInfo.UserPassportId.ToString().Length == 18)
                        result.Add(userPassportInfo);
                }
            }
            return result;

        }

        #endregion

        #region 私有方法

        private SqlParameter[] GetInsertParms(UserPassportInfo userInfo)
        {
            SqlParameter[] param ={
                  new SqlParameter("@UserPassportId",SqlDbType.BigInt,8),
                new SqlParameter("@UserName",SqlDbType.VarChar,40),               
                new SqlParameter("@Password",SqlDbType.VarChar,20), 
                
                new SqlParameter("@LoginCount",SqlDbType.Int,4),                                      
                new SqlParameter("@LastLogin",SqlDbType.DateTime),
                new SqlParameter("@DateCreated",SqlDbType.DateTime),
                new SqlParameter("@Status",SqlDbType.Int,4)
            };
            return SetInsertParms(param, userInfo);
        }

        private SqlParameter[] SetInsertParms(SqlParameter[] param, UserPassportInfo userInfo)
        {
            param[0].Value = userInfo.UserPassportId;
            param[1].Value = userInfo.UserName;
            param[2].Value = userInfo.Password;
            param[3].Value = userInfo.LoginCount;
            param[4].Value = userInfo.LastLogin;
            param[5].Value = userInfo.DateCreated;
            param[6].Value = userInfo.Status;
            return SetInsertParmsCheck(param);
        }



        /// <summary>
        ///取得datareader参数
        ///</summary>
        ///<param name="reader">datareader</param>
        ///<returns>用户</returns>
        private UserPassportInfo GetInstanceByDataReader(SqlDataReader reader)
        {
            UserPassportInfo instance = new UserPassportInfo();
            //UserPassportId,UserName,Password,Status,LoginCount,LastLogin,DateCreated FROM UserPassport
            instance.UserPassportId = reader.IsDBNull(0) ? 0 : reader.GetInt64(0);
            instance.UserName = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
            instance.Password = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
            instance.Status = reader.IsDBNull(3) ? 0 : reader.GetInt32(3);
            instance.LoginCount = reader.IsDBNull(4) ? 0 : reader.GetInt32(4);
            instance.LastLogin = reader.IsDBNull(5) ? DateTime.MinValue : reader.GetDateTime(5);
            instance.DateCreated = reader.IsDBNull(6) ? DateTime.MinValue : reader.GetDateTime(6);


            return instance;
        }

        /// <summary>
        /// 检查属性值是否为空
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        private SqlParameter[] SetInsertParmsCheck(SqlParameter[] param)
        {
            for (int i = 0; i < param.Length; i++)
            {
                if (param[i].Value == null)
                {
                    param[i].Value = DBNull.Value;
                }
                if (param[i].DbType == DbType.DateTime && param[i].Value.Equals(DateTime.MinValue))
                {
                    param[i].Value = DBNull.Value;
                }
            }
            return param;
        }

        #endregion

        public List<UserPassportInfo> GetUserPassportInfoByUserType(int UserType)
        {
            List<UserPassportInfo> result = new List<UserPassportInfo>();
            SqlParameter[] parms = {
                    new SqlParameter("@UserType",SqlDbType.Int)
                };
            parms[0].Value = UserType;

            UserPassportInfo userPassportInfo = null;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_PASSPORT_BY_USERTYPE, parms))
            {
                while (reader.Read())
                {
                    userPassportInfo = GetInstanceByDataReader(reader);
                    if (userPassportInfo != null && userPassportInfo.UserPassportId.ToString().Length == 18)
                        result.Add(userPassportInfo);
                }
            }
            return result;

        }
    }
}
